﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Text.RegularExpressions;

namespace CH.Study.ConsoleApp.Excel
{
    public class ExcelTest : ITest
    {
        public void Start()
        {
            string file = AppDomain.CurrentDomain.BaseDirectory + "Excel\\罗氏注册老客户.xlsx";
            string vcard = AppDomain.CurrentDomain.BaseDirectory + "Excel\\罗氏注册老客户.vcf";
            if (File.Exists(vcard))
            {
                File.Delete(vcard);
            }
            DataTable dt = ExcelToDataTable(file, "注册单（200-299）");
            DataTableToVCard(dt, vcard);
        }

        private void DataTableToVCard(DataTable dt, string vcard)
        {

            FileStream fs = new FileStream(vcard, FileMode.Append);
            StreamWriter sw = new StreamWriter(fs);

            foreach (DataRow row in dt.Rows)
            {
                string item = string.Empty;
                try
                {
                    if (row["电话1"] != null && !string.IsNullOrEmpty(row["电话1"].ToString()))
                    {
                        item += "BEGIN:VCARD" + Environment.NewLine;
                        item += "FN:" + row["单号"] + row["联系人"] + Environment.NewLine;
                        item += "TEL;TYPE=CELL:" + row["电话1"] + Environment.NewLine;
                        item += "EMAIL;TYPE=WORK:" + Environment.NewLine;
                        item += "X-QQ;TYPE=HOME:" + row["QQ"] + Environment.NewLine;
                        item += "ADR;TYPE=WORK:;;" + Filter(row["地址"]) + ";;;;" + Environment.NewLine;
                        item += "CATEGORIES:工作" + Environment.NewLine;
                        item += "NOTE:" + Filter(row["其他"]) + Environment.NewLine;
                        item += "ORG;TYPE=WORK:" + Filter(row["公司名"]) + Environment.NewLine;
                        item += "ORG;TYPE=CUSTOM:" + Filter(row["注册时间"]) + Environment.NewLine;
                        item += "ORG;TYPE=CUSTOM:" + Filter(row["备注"]) + Environment.NewLine;
                        item += "X-WDJ-STARRED:0" + Environment.NewLine;
                        item += "END:VCARD" + Environment.NewLine;
                        sw.Write(item);
                    }

                }
                catch (Exception)
                {

                }

            }
            sw.Close();
            sw.Dispose();
            fs.Close();
            fs.Dispose();
        }
        private string Filter(object obj)
        {
            if (obj == null)
            {
                return "";
            }

            if (string.IsNullOrEmpty(obj.ToString()))
            {
                return "";
            }
            else
            {
                Regex r = new Regex("[\n]");
                return r.Replace(obj.ToString(), "");
            }
        }
        public DataTable ExcelToDataTable(string pathName, string sheetName)
        {
            DataTable tbContainer = new DataTable();
            string strConn = string.Empty;
            if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
            FileInfo file = new FileInfo(pathName);
            if (!file.Exists) { throw new Exception("文件不存在"); }
            string extension = file.Extension;
            switch (extension)
            {
                case ".xls":
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
            }
            //链接Excel
            OleDbConnection cnnxls = new OleDbConnection(strConn);
            //读取Excel里面有 表Sheet1
            OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
            DataSet ds = new DataSet();
            //将Excel里面有表内容装载到内存表中！
            oda.Fill(tbContainer);
            return tbContainer;
        }
    }
}
